from odoo import fields, models


class EngineeringApproveReport(models.Model):
    _name = 's.engineering.approve.report'
    _description = 'Engineering Approve Report'
    _auto = False

    sale_name = fields.Char('sale name')
    product_name = fields.Char('product name')
    approval_date = fields.Date('approval date')
    approval_state = fields.Selection([('1', 'approval'), ('0', 'not approval')], string='approval state')

    @property
    def _table_query(self):
        # sql = """
        #     WITH date_series AS (
        #     -- 生成日期序列（从提交审批时间到审批时间）
        #     SELECT
        #         so.id so_id,
        #         generate_series(
        #             sw.create_date::DATE,          -- 提交审批日期
        #             COALESCE(swi1.approval_time, NOW())::DATE,  -- 审批日期或当前日期
        #             '1 day'                       -- 每天生成一条记录
        #         )::DATE AS record_date
        #     FROM
        #         sale_order so
        #     LEFT JOIN sb_workflow sw ON so.id = sw.sale_order_id
        #     LEFT JOIN sb_workflow_item swi1 ON sw.id = swi1.sb_workflow_id AND swi1.name = '工程审批'
        #     WHERE sw.state != 'fail_to_approve'
        # ),
        # daily_status AS (
        #     -- 判断每天的状态是否为 "已审批"
        #     SELECT
        #         ds.record_date,
        #         CASE
        #             WHEN swi1.approval_time is null then '0'
        #             WHEN ds.record_date = COALESCE(swi1.approval_time::DATE, NOW()::DATE) THEN '1'
        #             ELSE '0'
        #         END AS status
        #     FROM date_series ds
        #     LEFT JOIN sale_order so ON ds.so_id = so.id
        #     LEFT JOIN sb_workflow sw ON so.id = sw.sale_order_id
        #     LEFT JOIN sb_workflow_item swi1 ON sw.id = swi1.sb_workflow_id AND swi1.name = '工程审批'
        #     where DATE_TRUNC('month', sw.create_date) = DATE_TRUNC('month', now())
        # )
        # SELECT
        #     ds.record_date approval_date,
        #     ds.status approval_state
        # FROM
        #     daily_status ds
        # """

        sql = """
            WITH date_series AS (
                SELECT
                    so.id AS so_id,
                    generate_series(
                        sw.create_date::DATE,          
                        COALESCE(swi1.approval_time, NOW())::DATE,  
                        '1 day'                       
                    )::DATE AS record_date
                FROM
                    sale_order so
                LEFT JOIN
                    sb_workflow sw ON so.id = sw.sale_order_id
                LEFT JOIN
                    sb_workflow_item swi1 ON sw.id = swi1.sb_workflow_id AND swi1.name = '工程审批'
                WHERE
                    sw.state != 'fail_to_approve'
                    AND sw.create_date >= CURRENT_DATE - INTERVAL '1 month'  -- 当前时间到一个月前
            ),
            daily_status AS (
                SELECT
                    ds.record_date,
                    CASE
                        WHEN swi1.approval_time IS NULL THEN '0'
                        WHEN ds.record_date = COALESCE(swi1.approval_time::DATE, NOW()::DATE) THEN '1'
                        ELSE '0'
                    END AS status
                FROM
                    date_series ds
                LEFT JOIN
                    sale_order so ON ds.so_id = so.id
                LEFT JOIN
                    sb_workflow sw ON so.id = sw.sale_order_id
                LEFT JOIN
                    sb_workflow_item swi1 ON sw.id = swi1.sb_workflow_id AND swi1.name = '工程审批'
                WHERE
                    sw.create_date >= CURRENT_DATE - INTERVAL '1 month' 
            )
            SELECT
                ds.record_date AS approval_date,
                ds.status AS approval_state
            FROM
                daily_status ds
        """
        return sql
